package com.youlu.campus.service.data.impl;

import com.alibaba.fastjson.JSON;
import com.mongodb.client.result.DeleteResult;
import com.mongodb.client.result.UpdateResult;
import com.youlu.campus.common.utils.DoubleUtils;

import com.youlu.campus.entity.*;
import com.youlu.campus.entity.VO.req.UniversityStatisticsQueryVO;
import com.youlu.campus.entity.data.OrderPriceStatistics;
import com.youlu.campus.entity.data.UniversityStatistics;
import com.youlu.campus.service.data.UniversityStatisticsService;
import com.youlu.campus.service.file.FileExportTaskService;
import com.youlu.campus.service.file.FileService;
import com.youlu.campus.service.system.SystemOptLogService;
import com.youlu.campus.service.university.UniversityInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.repository.support.PageableExecutionUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Objects;

@Slf4j
@Service
public class UniversityStatisticsServiceImpl implements UniversityStatisticsService {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private RedisTemplate<String, String> redisTemplate;
    @Autowired
    private FileExportTaskService fileExportTaskService;
    @Autowired
    private SystemOptLogService systemOptLogService;
    @Autowired
    private FileService fileService;
    @Autowired
    private UniversityInfoService universityInfoService;
    @Value("${sys.fileExportPath:/data/file/campus-admin/}")
    private String fileExportPath;

    private HashMap<String, UniversityInfo> universityInfoHashMap = new HashMap<>();

    @Override
    public UniversityStatistics create(UniversityStatistics req) {
        req.setId(null);
        req.setCreatedTime(new Date());
        req = mongoTemplate.insert(req);
        return req;
    }

    @Override
    public boolean update(UniversityStatistics req) {
        Query query = new Query().addCriteria(Criteria.where("id").is(req.getId()));
        Update update = new Update();
        if (Objects.nonNull(req.getGmv())) {
            update.inc("gmv", req.getGmv());
        }
        if (Objects.nonNull(req.getPayNum())) {
            update.inc("payNum", req.getPayNum());
        }
        if (Objects.nonNull(req.getRefundGmv())) {
            update.inc("refundGmv", req.getRefundGmv());
        }
        if (Objects.nonNull(req.getRefundNum())) {
            update.inc("refundNum", req.getRefundNum());
        }
        if (Objects.nonNull(req.getRepurchaseDatas()) && req.getRepurchaseDatas().size() > 0) {
            update.set("repurchaseDatas", req.getRepurchaseDatas());
        }
        if (Objects.nonNull(req.getSignUpDatas()) && req.getSignUpDatas().size() > 0) {
            update.set("signUpDatas", req.getSignUpDatas());
        }
        if (Objects.nonNull(req.getSignUpNum())) {
            update.inc("signUpNum", req.getSignUpNum());
        }
        update.set("updatedTime", new Date());
        UpdateResult updateResult = mongoTemplate.updateFirst(query, update, UniversityStatistics.class);
        return updateResult.getModifiedCount() > 0 ? true : false;
    }

    @Override
    public boolean delete(String id) {
        Query query = new Query().addCriteria(Criteria.where("universityId").is(id));
        DeleteResult deleteResult = mongoTemplate.remove(query, UniversityStatistics.class);
        return deleteResult.getDeletedCount() > 0 ? true : false;
    }

    @Override
    public UniversityStatistics findByUniversityId(String universityId) {
        Query query = new Query().addCriteria(Criteria.where("universityId").is(universityId));
        UniversityStatistics req = mongoTemplate.findOne(query, UniversityStatistics.class);
        return req;
    }

    @Override
    public void export(UniversityStatisticsQueryVO req) {
        log.info(":>>> 开始导出大学数据统计");
        Query query1 = new Query().addCriteria(Criteria.where("id").ne(""));
        List<UniversityStatistics> datas = mongoTemplate.find(query1, UniversityStatistics.class);
        log.info(":>>> 高校数据数量:{}", datas.size());
        FileExportTask fileExportTask = new FileExportTask();
        fileExportTask.setToken(req.getOptUserName());
        fileExportTask.setBizType("order");
        fileExportTask.setStatus(0);
        fileExportTask.setOptUser(req.getOptUserName());
        fileExportTask.setOptUserId(req.getUserId());

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());
        String fileName = "高校数据统计-" + dateStr;
        String f = fileName + new Date().getTime() + ".xlsx";
        fileExportTask.setFileName(f);
        fileExportTask.setNum(datas.size());
        fileExportTask.setDesc("高校数据统计");
        String re = fileExportTaskService.create(fileExportTask);
        SystemOptLog optlog = new SystemOptLog();
        optlog.setIp(req.getIpAddress());
        optlog.setOptUserName(req.getOptUserName());
        optlog.setOptTime(new Date());
        optlog.setBizType("university-data");
        optlog.setOptRealName(req.getOptUserRealName());
        optlog.setSqlDetai(JSON.toJSONString(query1));
        optlog.setDesc("导出高校数据统计文件名:" + f + ",数据总条数:" + datas.size());
        systemOptLogService.create(optlog);
        this.exportUniversityStatistics(query1, Long.valueOf(datas.size()), f, re);
    }

    @Override
    public Page<UniversityStatistics> list(UniversityStatisticsQueryVO req) {
        Query query = new Query();
        if (StringUtils.isNotBlank(req.getUniversityId())) {
            query.addCriteria(Criteria.where("universityId").is(req.getUniversityId()));
        }
        if (StringUtils.isNotBlank(req.getProvince())) {
            query.addCriteria(Criteria.where("province").is(req.getProvince()));
        }
        if (Objects.nonNull(req.getComplaintRatioStart())) {
            query.addCriteria(Criteria.where("complaintRatio").gte(req.getComplaintRatioStart()));
        }
        if (Objects.nonNull(req.getConversionRatioStart())) {
            query.addCriteria(Criteria.where("conversionRatio").gte(req.getConversionRatioStart()));
        }
        if (Objects.nonNull(req.getPenetrationRatioStart())) {
            query.addCriteria(Criteria.where("penetrationRatio").gte(req.getPenetrationRatioStart()));
        }
        Long count = mongoTemplate.count(query, UniversityStatistics.class);
        log.info(":>>> 高校的数量:{}，查询条件:{}", count, JSON.toJSON(query));
        PageRequest pageRequest = PageRequest.of(req.getPage() - 1, req.getPageSize());
//        query.with(new Sort(Sort.Direction.DESC, "payNum"));
        Sort sort=Sort.by(Sort.Direction.DESC, "payNum");
        query.with(sort);
        List<UniversityStatistics> us = mongoTemplate.find(query.with(pageRequest), UniversityStatistics.class);
        for (UniversityStatistics universityStatistics : us) {
            if (Objects.nonNull(universityStatistics.getGmv())) {
                universityStatistics.setGmv(DoubleUtils.fun1(universityStatistics.getGmv(), 2));
            }
            if (Objects.nonNull(universityStatistics.getRefundGmv())) {
                universityStatistics.setRefundGmv(DoubleUtils.fun1(universityStatistics.getRefundGmv(), 2));
            }
            if (Objects.nonNull(universityStatistics.getRefundNum()) && Objects.nonNull(universityStatistics.getPayNum())) {
                universityStatistics.setRefundRatio(DoubleUtils.fun1(universityStatistics.getRefundNum() * 100D / universityStatistics.getPayNum(), 2));
            }
            if (Objects.nonNull(universityStatistics.getPayNum()) && Objects.nonNull(universityStatistics.getSignUpNum())) {
                universityStatistics.setConversionRatio(DoubleUtils.fun1(universityStatistics.getPayNum() * 100D / universityStatistics.getSignUpNum(), 2));
            }
            if (Objects.nonNull(universityStatistics.getTotal()) && Objects.nonNull(universityStatistics.getSignUpNum())) {
                universityStatistics.setPenetrationRatio(DoubleUtils.fun1(universityStatistics.getSignUpNum() * 100D / universityStatistics.getTotal(), 2));
            }
            if (Objects.nonNull(universityStatistics.getComplaintNum()) && Objects.nonNull(universityStatistics.getPayNum())) {
                universityStatistics.setComplaintRatio(DoubleUtils.fun1(universityStatistics.getComplaintNum() * 100D / universityStatistics.getPayNum(), 2));
            }
            if (Objects.nonNull(universityStatistics.getGmv()) && Objects.nonNull(universityStatistics.getPayNum())) {
                universityStatistics.setPpc(DoubleUtils.fun1(universityStatistics.getGmv() / universityStatistics.getPayNum(), 2));
            }
        }
        return PageableExecutionUtils.getPage(us, pageRequest, () -> count);
    }

    @Override
    public UniversityStatistics findOne(String id) {
        Query query = new Query().addCriteria(Criteria.where("id").is(id));
        UniversityStatistics u = mongoTemplate.findOne(query, UniversityStatistics.class);
        return u;
    }

    @Override
    public void allData(UniversityStatisticsQueryVO req) {
        Query query = new Query().addCriteria(Criteria.where("status").ne("0"));
        if (Objects.nonNull(req)) {
            Criteria criteriaPay = null;
            if (req.getStartTime() != null || req.getEndTime() != null) {
                criteriaPay = Criteria.where("payTime");
                if (req.getStartTime() != null) {
                    criteriaPay.gte(req.getStartTime());
                }
                if (req.getEndTime() != null) {
                    criteriaPay.lte(req.getEndTime());
                }
            }
            if (Objects.nonNull(criteriaPay)) {
                query.addCriteria(criteriaPay);
            }
        }
        Long count = mongoTemplate.count(query, OrderInfo.class);
        Integer pageSize = 10000;
        Integer pages = count.intValue() / pageSize + 1;
        Integer counter = 0;
        HashMap<String, Long> map = new HashMap<>();
        for (int page = 0; page < pages; page++) {
            log.info(":>>> 开始处理第:{} 页订单,第:{} 条数据", page, counter);
            query.limit(pageSize);
            query.skip(page * pageSize);
            List<OrderInfo> orderInfos = mongoTemplate.find(query, OrderInfo.class);
            if (CollectionUtils.isEmpty(orderInfos)) {
                continue;
            }
            for (OrderInfo orderInfo : orderInfos) {
                if (StringUtils.isBlank(orderInfo.getUniversityId()) || StringUtils.isBlank(orderInfo.getMobile())) {
                    log.error(":>>> 订单:{} 大学ID或手机号为空");
                    continue;
                }
                Long c = this.statisticsUniversityOrder(orderInfo, map.get(orderInfo.getMobile()));
                if (!map.containsKey(orderInfo.getMobile())) {
                    map.put(orderInfo.getMobile(), c);
                }

            }
        }
    }

    @Override
    public Long statisticsUniversityOrder(OrderInfo orderInfo, Long count) {
        log.info(":>>> 开始统计订单数据:{},{}", orderInfo.getOrderNo(), count);
        if (Objects.isNull(count)) {
            Query query = new Query().addCriteria(Criteria.where("mobile").is(orderInfo.getMobile()).and("universityId").is(orderInfo.getUniversityId()).and("status").ne("0"));
            count = mongoTemplate.count(query, OrderInfo.class);
        }
        Integer c = count.intValue();
        UniversityStatistics u = this.findByUniversityId(orderInfo.getUniversityId());
        UniversityInfo universityInfo = universityInfoHashMap.get(orderInfo.getUniversityId());
        if (Objects.isNull(universityInfo)) {
            universityInfo = universityInfoService.findOne(orderInfo.getUniversityId());
        }
        Double gmv = Double.valueOf("" + orderInfo.getTotalFee()) / 100;
        if (Objects.isNull(u)) {
            log.info(":>>> 大学:{} 统计数据不存在", orderInfo.getUniversityId());
            u = new UniversityStatistics();
            u.setUniversityId(orderInfo.getUniversityId());
            u.setUniversityName(universityInfo.getName());
            u.setProvince(universityInfo.getProvince());
            u.setGmv(gmv);
            u.setPayNum(1L);
            if (Objects.nonNull(orderInfo.getVerifyStatus())) {
                if ((1 == orderInfo.getVerifyStatus().intValue()) || (2 == orderInfo.getVerifyStatus().intValue())) {
                    u.setRefundNum(1L);
                    u.setRefundGmv(gmv);
                }
            }
            try {
                if (StringUtils.isNotBlank(universityInfo.getUserCount()) && StringUtils.isNumeric(universityInfo.getUserCount())) {
                    u.setTotal(Long.valueOf(universityInfo.getUserCount()));
                }

            } catch (Exception e) {
                log.info(":>>> 大学:{} 总人数错误", e);
            }
            u.getRepurchaseDatas().put(c, 1L);
            this.create(u);
            return count;
        }
        UniversityStatistics update = new UniversityStatistics();
        update.setId(u.getId());
        update.setGmv(gmv);
        update.setPayNum(1L);
        if (Objects.nonNull(orderInfo.getVerifyStatus())) {
            if ((1 == orderInfo.getVerifyStatus().intValue()) || (2 == orderInfo.getVerifyStatus().intValue())) {
                update.setRefundNum(1L);
                update.setRefundGmv(gmv);
            }

        }
        if (u.getRepurchaseDatas().containsKey(c)) {
            log.info(":>>> 大学已经存在购买记录:{},{},累加", universityInfo.getName(), c, u.getRepurchaseDatas().get(c) + 1L);
            u.getRepurchaseDatas().put(c, u.getRepurchaseDatas().get(c) + 1L);
        } else {
            u.getRepurchaseDatas().put(c, 1L);
            log.info(":>>> 大学不存在购买记录:{},{},累加", universityInfo.getName(), c, u.getRepurchaseDatas().get(c) + 1L);
        }
        update.setRepurchaseDatas(u.getRepurchaseDatas());
        this.update(update);
        return count;
    }

    @Override
    public void allSignUpData(String activityId, UniversityStatisticsQueryVO req) {
        log.info(":>>> 开始补充活动报名全数据");
        Query query = new Query().addCriteria(Criteria.where("joined").is(Boolean.TRUE));
        if (StringUtils.isNotBlank(activityId)) {
            query.addCriteria(Criteria.where("activityId").is(activityId));
        }
        if (Objects.nonNull(req)) {
            Criteria criteriaPay = null;
            if (req.getStartTime() != null || req.getEndTime() != null) {
                criteriaPay = Criteria.where("signUpTime");
                if (req.getStartTime() != null) {
                    criteriaPay.gte(req.getStartTime());
                }
                if (req.getEndTime() != null) {
                    criteriaPay.lte(req.getEndTime());
                }
            }
            if (Objects.nonNull(criteriaPay)) {
                query.addCriteria(criteriaPay);
            }
        }
        Long count = mongoTemplate.count(query, UserParticipateActivityInfo.class);
        Integer pageSize = 10000;
        Integer pages = count.intValue() / pageSize + 1;
        Integer counter = 0;
        HashMap<String, Long> map = new HashMap<>();
        for (int page = 0; page < pages; page++) {
            log.info(":>>> 开始处理第:{} 页订单,第:{} 条数据", page, counter);
            query.limit(pageSize);
            query.skip(page * pageSize);
            List<UserParticipateActivityInfo> ups = mongoTemplate.find(query, UserParticipateActivityInfo.class);
            if (CollectionUtils.isEmpty(ups)) {
                continue;
            }
            for (UserParticipateActivityInfo up : ups) {
                log.info(":>>> 开始处理第:{} 页订单,第:{} 条数据", page, counter);
                if (StringUtils.isBlank(up.getUniversityId()) || StringUtils.isBlank(up.getMobile())) {
                    log.error(":>>> 订单:{} 大学ID或手机号为空");
                    continue;
                }
                Long c = this.statisticsUniversitySignUp(up, map.get(up.getMobile()), activityId);
                if (!map.containsKey(up.getMobile())) {
                    map.put(up.getMobile(), c);
                }
                counter++;
            }
        }
    }

    @Override
    public Long statisticsUniversitySignUp(UserParticipateActivityInfo orderInfo, Long count, String activityId) {
        log.info(":>>> 开始统计报名数据:{},{}", orderInfo.getMobile(), count);
        if (Objects.isNull(count)) {
            Query query = new Query().addCriteria(Criteria.where("mobile").is(orderInfo.getMobile()).and("universityId").is(orderInfo.getUniversityId()).and("joined").is(Boolean.TRUE));
            count = mongoTemplate.count(query, UserParticipateActivityInfo.class);
        }
        Integer c = count.intValue();
        UniversityStatistics u = this.findByUniversityId(orderInfo.getUniversityId());
        UniversityInfo universityInfo = universityInfoHashMap.get(orderInfo.getUniversityId());
        if (Objects.isNull(universityInfo)) {
            universityInfo = universityInfoService.findOne(orderInfo.getUniversityId());
        }
        if (Objects.isNull(u)) {
            log.info(":>>> 大学:{} 统计数据不存在", orderInfo.getUniversityId());
            u = new UniversityStatistics();
            u.setUniversityId(orderInfo.getUniversityId());
            u.setUniversityName(universityInfo.getName());
            u.setProvince(universityInfo.getProvince());
            try {
                if (StringUtils.isNotBlank(universityInfo.getUserCount()) && StringUtils.isNumeric(universityInfo.getUserCount())) {
                    u.setTotal(Long.valueOf(universityInfo.getUserCount()));
                }
            } catch (Exception e) {
                log.info(":>>> 大学:{} 总人数错误", e);
            }
            u.setSignUpNum(1L);
            u.getSignUpDatas().put(c, 1L);
            this.create(u);
            return count;
        }
        UniversityStatistics update = new UniversityStatistics();
        update.setId(u.getId());
        update.setSignUpNum(1L);
        if (u.getSignUpDatas().containsKey(c)) {
            log.info(":>>> 大学已经存在报名记录:{},{},累加", universityInfo.getName(), c, u.getSignUpDatas().get(c) + 1L);
            u.getSignUpDatas().put(c, u.getSignUpDatas().get(c) + 1L);
        } else {
            u.getSignUpDatas().put(c, 1L);
            log.info(":>>> 大学不存在报名记录:{},{},不累加", universityInfo.getName(), c, 1L);
        }
        update.setSignUpDatas(u.getSignUpDatas());
        this.update(update);
        return count;
    }

    /**
     * 导出发货订单
     *
     * @param query
     * @param count
     * @param f
     * @param exportTaskId
     */
        private void exportUniversityStatistics(Query query, Long count, String f, String exportTaskId) {
        int counter = 0;
        XSSFWorkbook workbook = new XSSFWorkbook();
        try {
            int rowNum = 1;
            int sheetNum = 1;
            //创建一个Excel表单,参数为sheet的名字
            XSSFSheet sheet = workbook.createSheet("sheet" + "-" + sheetNum);
            setUniversityStatisticsExcelTitle(workbook, sheet);
            //创建表头
            XSSFRow row = null;
            List<UniversityStatistics> contents = mongoTemplate.find(query, UniversityStatistics.class);
            if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(contents)) {
                for (UniversityStatistics c : contents) {
                    log.info(":>>> 导出行数:{}", counter);
                    if (counter != 0 && counter % 990000 == 0) {
                        //超过990000就自动创建一个sheet
                        sheetNum++;
                        sheet = workbook.createSheet("sheet-" + sheetNum);
                        setUniversityStatisticsExcelTitle(workbook, sheet);
                        //创建表头
                        //新增数据行，并且设置单元格数据
                        rowNum = 1;
                        sheetNum++;
                        log.info(":>>> 操作600000行,创建新sheet");
                    }
                    row = sheet.createRow(rowNum);
                    //  第一列
                    UniversityInfo universityInfo = null;
                    if (StringUtils.isNotBlank(c.getUniversityName())) {
                        row.createCell(0).setCellValue(c.getUniversityName());
                    }
                    if (StringUtils.isNotBlank(c.getUniversityId())) {
                        universityInfo = universityInfoService.findOne(c.getUniversityId());
                        if (Objects.nonNull(universityInfo)) {
                            row.createCell(1).setCellValue(universityInfo.getProvince());
                        }
                    }
                    if (Objects.nonNull(c.getSignUpNum())) {
                        row.createCell(2).setCellValue(c.getSignUpNum());
                    }
                    if (Objects.nonNull(c.getPayNum())) {
                        row.createCell(3).setCellValue(c.getPayNum());
                    }
                    if (Objects.nonNull(c.getPayNum()) && Objects.nonNull(c.getSignUpNum())) {
                        Double ratio = Double.valueOf(Double.valueOf(c.getPayNum()) / Double.valueOf(c.getSignUpNum()));
                        row.createCell(4).setCellValue(ratio);
                    }
                    if (Objects.nonNull(c.getRefundNum()) && Objects.nonNull(c.getSignUpNum())) {
                        Double ratio = Double.valueOf(Double.valueOf(c.getRefundNum()) / Double.valueOf(c.getSignUpNum()));
                        row.createCell(5).setCellValue(ratio);
                    }
                    if (Objects.nonNull(c.getRefundNum())) {
                        row.createCell(6).setCellValue(c.getRefundNum());
                    }
                    if (Objects.nonNull(c.getGmv())) {
                        row.createCell(7).setCellValue(c.getGmv());
                    }
                    if (Objects.nonNull(c.getRefundGmv())) {
                        row.createCell(8).setCellValue(c.getRefundGmv());
                    }
                    if (Objects.nonNull(c.getGmv()) && Objects.nonNull(c.getPayNum())) {
                        Double p = Double.valueOf(Double.valueOf(c.getGmv()) / Double.valueOf(c.getPayNum()));
                        row.createCell(9).setCellValue(p);
                    }
                    if (c.getSignUpDatas().size() != 0) {
                        if (c.getSignUpDatas().containsKey(1)) {
                            row.createCell(10).setCellValue(c.getSignUpDatas().get(1));
                        }
                        if (c.getSignUpDatas().containsKey(2)) {
                            row.createCell(11).setCellValue(c.getSignUpDatas().get(2));
                        }
                        if (c.getSignUpDatas().containsKey(3)) {
                            row.createCell(12).setCellValue(c.getSignUpDatas().get(3));
                        }
                        if (c.getSignUpDatas().containsKey(4)) {
                            row.createCell(13).setCellValue(c.getSignUpDatas().get(4));
                        }
                        if (c.getSignUpDatas().containsKey(5)) {
                            row.createCell(14).setCellValue(c.getSignUpDatas().get(5));
                        }
                        if (c.getSignUpDatas().containsKey(6)) {
                            row.createCell(15).setCellValue(c.getSignUpDatas().get(5));
                        }
                        if (c.getSignUpDatas().containsKey(7)) {
                            row.createCell(16).setCellValue(c.getSignUpDatas().get(7));
                        }
                        if (c.getSignUpDatas().containsKey(8)) {
                            row.createCell(17).setCellValue(c.getSignUpDatas().get(8));
                        }
                        if (c.getSignUpDatas().containsKey(9)) {
                            row.createCell(18).setCellValue(c.getSignUpDatas().get(9));
                        }
                        if (c.getSignUpDatas().containsKey(10)) {
                            row.createCell(19).setCellValue(c.getSignUpDatas().get(10));
                        }
                        if (c.getRepurchaseDatas().size() != 0) {
                            if (c.getRepurchaseDatas().containsKey(1)) {
                                row.createCell(20).setCellValue(c.getRepurchaseDatas().get(1));
                            }
                            if (c.getRepurchaseDatas().containsKey(2)) {
                                row.createCell(21).setCellValue(c.getRepurchaseDatas().get(2));
                            }
                            if (c.getRepurchaseDatas().containsKey(3)) {
                                row.createCell(22).setCellValue(c.getRepurchaseDatas().get(3));
                            }
                            if (c.getRepurchaseDatas().containsKey(4)) {
                                row.createCell(23).setCellValue(c.getRepurchaseDatas().get(4));
                            }
                            if (c.getRepurchaseDatas().containsKey(5)) {
                                row.createCell(24).setCellValue(c.getRepurchaseDatas().get(5));
                            }
                            if (c.getRepurchaseDatas().containsKey(6)) {
                                row.createCell(25).setCellValue(c.getRepurchaseDatas().get(6));
                            }
                            if (c.getRepurchaseDatas().containsKey(7)) {
                                row.createCell(26).setCellValue(c.getRepurchaseDatas().get(7));
                            }
                            if (c.getRepurchaseDatas().containsKey(8)) {
                                row.createCell(27).setCellValue(c.getRepurchaseDatas().get(8));
                            }
                            if (c.getRepurchaseDatas().containsKey(9)) {
                                row.createCell(28).setCellValue(c.getRepurchaseDatas().get(9));
                            }
                            if (c.getRepurchaseDatas().containsKey(10)) {
                                row.createCell(29).setCellValue(c.getRepurchaseDatas().get(10));
                            }
                        }
                        if (Objects.nonNull(universityInfo) && Objects.nonNull(universityInfo.getUserCount())) {
                            row.createCell(30).setCellValue(universityInfo.getUserCount());
                            if (Objects.nonNull(c.getSignUpNum())) {
                                Double p = Double.valueOf(Double.valueOf(c.getSignUpNum()) / Double.valueOf(universityInfo.getUserCount()));
                                row.createCell(31).setCellValue(p);
                            }
                        }
                    }
                    rowNum++;
                }
            }
            //将excel写入到输出流中
            log.info(":>>> flush:{} 行", rowNum);
            OutputStream outputStream = new FileOutputStream(fileExportPath + f);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            log.info(":>>> 写文件到本地然后读:{}", f);
            File file = new File(fileExportPath + f);
            InputStream in = new FileInputStream(file);
            String url = fileService.uploadFile(in, f);
            Query queryTask = new Query().addCriteria(Criteria.where("id").is(exportTaskId));
            Update update = new Update();
            update.set("url", url);
            update.set("status", 1);
            update.set("updatedTime", new Date());
            UpdateResult updateResult = mongoTemplate.updateFirst(queryTask, update, FileExportTask.class);
            log.info(":>>> 更新任务:{},状态:{}", exportTaskId, updateResult.getModifiedCount() > 0 ? true : false);
            in.close();
            log.info(":>>> 大学统计内容导出,关闭文件流");
        } catch (Exception e) {
            log.error(":>>> 大学统计导出错误:{}", e);
        }
    }

    private void setUniversityStatisticsExcelTitle(XSSFWorkbook workbook, XSSFSheet sheet) {
        XSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);

        //设置为居中加粗
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        style.setFont(font);

        XSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("高校名");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("所在省");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("报名数量");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("支付数量");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("转化率");
        cell.setCellStyle(style);

        cell = row.createCell(5);
        cell.setCellValue("退款率");
        cell.setCellStyle(style);


        cell = row.createCell(6);
        cell.setCellValue("退款人数");
        cell.setCellStyle(style);

        cell = row.createCell(7);
        cell.setCellValue("支付GMV");
        cell.setCellStyle(style);

        cell = row.createCell(8);
        cell.setCellValue("退款GMV");
        cell.setCellStyle(style);

        cell = row.createCell(9);
        cell.setCellValue("客单价");
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue("报名1次人数");
        cell.setCellStyle(style);
        cell = row.createCell(11);
        cell.setCellValue("报名2次人数");
        cell.setCellStyle(style);
        cell = row.createCell(12);
        cell.setCellValue("报名3次人数");
        cell.setCellStyle(style);
        cell = row.createCell(13);
        cell.setCellValue("报名4次人数");
        cell.setCellStyle(style);
        cell = row.createCell(14);
        cell.setCellValue("报名5次人数");
        cell.setCellStyle(style);
        cell = row.createCell(15);
        cell.setCellValue("报名6次人数");
        cell.setCellStyle(style);
        cell = row.createCell(16);
        cell.setCellValue("报名7次人数");
        cell.setCellStyle(style);
        cell = row.createCell(17);
        cell.setCellValue("报名8次人数");
        cell.setCellStyle(style);
        cell = row.createCell(18);
        cell.setCellValue("报名9次人数");
        cell.setCellStyle(style);
        cell = row.createCell(19);
        cell.setCellValue("报名10次人数");
        cell.setCellStyle(style);
        // 支付
        cell = row.createCell(20);
        cell.setCellValue("支付1次人数");
        cell.setCellStyle(style);
        cell = row.createCell(21);
        cell.setCellValue("支付2次人数");
        cell.setCellStyle(style);
        cell = row.createCell(22);
        cell.setCellValue("支付3次人数");
        cell.setCellStyle(style);
        cell = row.createCell(23);
        cell.setCellValue("支付4次人数");
        cell.setCellStyle(style);
        cell = row.createCell(24);
        cell.setCellValue("支付5次人数");
        cell.setCellStyle(style);
        cell = row.createCell(25);
        cell.setCellValue("支付6次人数");
        cell.setCellStyle(style);
        cell = row.createCell(26);
        cell.setCellValue("支付7次人数");
        cell.setCellStyle(style);
        cell = row.createCell(27);
        cell.setCellValue("支付8次人数");
        cell.setCellStyle(style);
        cell = row.createCell(28);
        cell.setCellValue("支付9次人数");
        cell.setCellStyle(style);
        cell = row.createCell(29);
        cell.setCellValue("支付10次人数");
        cell.setCellStyle(style);
        cell = row.createCell(30);
        cell.setCellValue("高校总人数");
        cell.setCellStyle(style);
        cell = row.createCell(31);
        cell.setCellValue("高校渗透率");
        cell.setCellStyle(style);


    }
}
